//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//********************************************************* 

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Research.DataLayer;
using Microsoft.TridentWordAddIn.Common;

namespace Microsoft.TridentWordAddIn.Server
{
    public sealed class ConnectionHelper
    {
        /// <summary>
        /// Prevents a default instance of the SqlConnectionHelper class from being created.
        /// </summary>
        private ConnectionHelper()
        {
        }

        /// <summary>
        /// Gets the SQL database list.
        /// </summary>
        /// <param name="isTrusted">if set to <c>true</c> [is trusted].</param>
        /// <param name="serverName">Name of the server.</param>
        /// <param name="userName">Name of the user.</param>
        /// <param name="password">The password.</param>
        /// <returns>List<string></returns>
        public static List<string> GetSqlDatabaseList(bool isTrusted, string serverName, string userName, string password)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.IntegratedSecurity = isTrusted;
            builder.DataSource = serverName;
            builder.InitialCatalog = "master";
            if (!isTrusted)
            {
                builder.UserID = userName;
                builder.Password = password;
            }

            List<string> databaseList = new List<string>();
            using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
            {
                using (SqlCommand command = new SqlCommand("sp_databases", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        string databaseName = string.Empty;
                        List<string> ignoreDatabaseList = new List<string>() { "master", "model", "msdb", "tempdb" };
                        while (reader.Read())
                        {
                            databaseName = Convert.ToString(reader["DATABASE_NAME"]);
                            if (!ignoreDatabaseList.Contains(databaseName))
                            {
                                databaseList.Add(databaseName);
                            }
                        }
                    }
                }
            }

            return databaseList;
        }

        /// <summary>
        /// Gets the connection.
        /// </summary>
        /// <param name="tridentConnection">The trident connection.</param>
        /// <returns>Connection</returns>
        public static Connection GetServerConnection(TridentConnection tridentConnection)
        {
            Connection connection = null;
            IConnMgrInfoParams connectionParameters = null;
            IConnMgrInfoParamsHelper connectionHelper = null;

            switch (tridentConnection.ConnectionType)
            {
                case ConnectionType.Sql:
                    connectionHelper = new SQLConnectionParameters.Helper();
                    connectionParameters = GetSqlParameters(tridentConnection.Name, tridentConnection.SqlParameters);
                    break;
               
            }

            if (connectionHelper != null)
            {
                try
                {
                    connection = connectionHelper.OpenConnection(connectionParameters);
                }
                catch (ConnectionFailure)
                {
                    return null;
                }
            }

            return connection;
        }

        /// <summary>
        /// Gets the SQL parameters.
        /// </summary>
        /// <param name="connectionName">Name of the connection.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>IConnMgrInfoParams</returns>
        public static IConnMgrInfoParams GetSqlParameters(string connectionName, TridentSqlConnectionParameters parameters)
        {
            if (parameters.IsTrusted)
            {
                return SQLConnectionParameters.CreateTrustedConnection(connectionName, parameters.ServerName, parameters.DatabaseName);
            }
            else
            {
                return SQLConnectionParameters.CreateUserPassConnection(
                    connectionName,
                    parameters.UserName,
                    parameters.Password,
                    parameters.ServerName,
                    parameters.DatabaseName);
            }
        }

        /// <summary>
        /// Gets the web service parameters.
        /// </summary>
        /// <param name="connectionName">Name of the connection.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>IConnMgrInfoParams</returns>
        //public static IConnMgrInfoParams GetWebServiceParameters(string connectionName, WebServiceParameters parameters)
        //{
        //    //return WSConnectionParameters.CreateUserPassConnection(
        //    //    connectionName,
        //    //    new Uri(parameters.Url),
        //    //    parameters.UserName,
        //    //    parameters.Password
        //    //    );
        //}
    }
}
